package com.boss.salary.dao.impl;

import com.boss.common.util.DateUtils;
import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.ISalaryPaymentTicketDao;
import com.boss.salary.entity.SalaryPaymentTicket;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static com.boss.sys.utils.SysUtils.inSql;

@Repository
public class SalaryPaymentTicketDaoImpl extends BaseDaoImpl implements ISalaryPaymentTicketDao {


    @Override
    public List<SalaryPaymentTicket> queryPaymentTicketPage(PaginationDTO paginationDTO,String mofDivCode,String agencyId,String flag, String agencyName, String payeeAcctNo, String payAppAmt, String payeeAcctName, String payeeAcctBankName) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select  id, biz_key, create_time, update_time, agency_id, agency_name, pay_id, ")
                .append("pay_app_no, bgt_id, pay_app_amt, fund_type, payee_acct_name,payee_acct_no,")
                .append("payee_acct_bank_name, pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, ")
                .append("use_name,is_deleted, mof_div_code, exp_func, return_time, return_reason, ")
                .append("is_push_again, revisit_reason_time, new_payee_acct_name, new_payee_acct_no from ")
                .append(TableName.AGENCY_PAYMENT_TICKET)
                .append(" where is_deleted = 0  and ")
                .append(" mof_div_code =? ");
                paramList.add(mofDivCode);
        if (!StringUtils.isEmpty(agencyId)){
            sql.append("and  agency_id=?");
            paramList.add(agencyId);
        }
        if (!StringUtils.isEmpty(agencyName)){
            sql.append(" and  agency_name like ? ");
            paramList.add("%"+agencyName+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctNo)){
            sql.append(" and  payee_acct_no like ? ");
            paramList.add("%"+payeeAcctNo+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctName)){
            sql.append(" and  payee_acct_name like ? ");
            paramList.add("%"+payeeAcctName+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctBankName)){
            sql.append(" and  payee_acct_bank_name like ? ");
            paramList.add("%"+payeeAcctBankName+"%");
        }
        if (!StringUtils.isEmpty(payAppAmt)){
            sql.append(" and  pay_app_amt = ? ");
            paramList.add(payAppAmt);
        }
        if(flag.equals("waitUpdate")){
        sql.append(" and is_push_again = 0 ");
        sql.append(" order by return_time");
        }
        if(flag.equals("submit")){
            sql.append(" and is_push_again = 1 ");
            sql.append(" order by revisit_reason_time Desc");
        }

        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(SalaryPaymentTicket.class), params);
    }

    @Override
    public int queryaymentTicket(String mofDivCode,String agencyId,String flag,String agencyName, String payeeAcctNo, String payAppAmt, String payeeAcctName, String payeeAcctBankName) {
        StringBuilder sql = new StringBuilder();
        List<Object> paramList = new ArrayList<>();
        sql.append("select count(*) from ").append(TableName.AGENCY_PAYMENT_TICKET)
                .append(" where is_deleted = 0  and ")
                .append(" mof_div_code =? ");
            paramList.add(mofDivCode);
        if (!StringUtils.isEmpty(agencyId)){
            sql.append("and  agency_id=?");
            paramList.add(agencyId);
        }
        if(flag.equals("waitUpdate")){
            sql.append(" and is_push_again = 0 ");
        }
        if(flag.equals("submit")){
            sql.append(" and is_push_again = 1 ");
        }
        if (!StringUtils.isEmpty(agencyName)){
            sql.append(" and  agency_name like ? ");
            paramList.add("%"+agencyName+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctNo)){
            sql.append(" and  payee_acct_no like ? ");
            paramList.add("%"+payeeAcctNo+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctName)){
            sql.append(" and  payee_acct_name like ? ");
            paramList.add("%"+payeeAcctName+"%");
        }
        if (!StringUtils.isEmpty(payeeAcctBankName)){
            sql.append(" and  payee_acct_bank_name like ? ");
            paramList.add("%"+payeeAcctBankName+"%");
        }
        if (!StringUtils.isEmpty(payAppAmt)){
            sql.append(" and  pay_app_amt = ? ");
            paramList.add(payAppAmt);
        }
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class,paramList.toArray());
    }

    @Override
    public void updatePaymentTicket(SalaryPaymentTicket salaryPaymentTicket) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_PAYMENT_TICKET);
        sql.append(" set  update_time = ?,new_payee_acct_name = ?, new_payee_acct_no = ?,payee_acct_bank_name=?")
                .append(" WHERE id = ?");
        Object[] params = new Object[]{
                salaryPaymentTicket.getUpdateTime(),salaryPaymentTicket.getNewPayeeAcctName(),salaryPaymentTicket.getNewPayeeAcctNo(),
                salaryPaymentTicket.getPayeeAcctBankName(),salaryPaymentTicket.getId()
        };
        this.getjdbcTemplate().update(sql.toString(), params);
    }

    @Override
    public void modifyIsPushAgain(List<Map<String,Object>> mapList) {
        StringBuilder sql = new StringBuilder();
        sql.append("update ").append(TableName.AGENCY_PAYMENT_TICKET);
        sql.append(" set  is_push_again = ? ,revisit_reason_time = ?").append(" WHERE id = ?");
        List<Object[]> paramsList = new ArrayList<>();
                for(Map map:mapList){
                    paramsList.add(new Object[]{
                            map.get("isPushAgain"),String.valueOf(DateUtils.getCurrentTimeStamp()),map.get("id")
                    });
                }
        this.getjdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 3; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
        }

    @Override
    public void insertPaymentTicket(List<SalaryPaymentTicket> salaryPaymentTickets) {
        StringBuilder sql = new StringBuilder();
        sql.append("Insert into ").append(TableName.AGENCY_PAYMENT_TICKET);
        sql.append(" (id, biz_key, create_time, update_time, agency_id, agency_name, ")
                .append(" pay_id, pay_app_no, bgt_id, pay_app_amt, fund_type, ")
                .append(" payee_acct_name, payee_acct_no, payee_acct_bank_name, ")
                .append(" pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, ")
                .append(" use_name, is_deleted, mof_div_code, exp_func, return_time, ")
                .append(" return_reason, is_push_again, revisit_reason_time, ")
                .append(" new_payee_acct_name, new_payee_acct_no) ")
                .append(" value (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
        List<Object[]> paramsList = new ArrayList<>(salaryPaymentTickets.size());
        for (SalaryPaymentTicket salaryPaymentTicket : salaryPaymentTickets){
            Object[] params = new Object[]{
                    salaryPaymentTicket.getId(),salaryPaymentTicket.getBizKey(),salaryPaymentTicket.getCreateTime(),
                    salaryPaymentTicket.getUpdateTime(),salaryPaymentTicket.getAgencyId(),salaryPaymentTicket.getAgencyName(),
                    salaryPaymentTicket.getPayId(),salaryPaymentTicket.getPayAppNo(),salaryPaymentTicket.getBgtId(),
                    salaryPaymentTicket.getPayAppAmt(),salaryPaymentTicket.getFundType(),salaryPaymentTicket.getPayeeAcctName(),
                    salaryPaymentTicket.getPayeeAcctNo(),salaryPaymentTicket.getPayeeAcctBankName(),salaryPaymentTicket.getPayType(),
                    salaryPaymentTicket.getProCat(),salaryPaymentTicket.getGovBgtEco(),salaryPaymentTicket.getDepBgtEco(),
                    salaryPaymentTicket.getPro(),salaryPaymentTicket.getUseName(),salaryPaymentTicket.getIsDeleted(),
                    salaryPaymentTicket.getMofDivCode(),salaryPaymentTicket.getExpFunc(),salaryPaymentTicket.getReturnTime(),
                    salaryPaymentTicket.getReturnReason(),salaryPaymentTicket.getIsPushAgain(),salaryPaymentTicket.getRevisitReasonTime(),
                    salaryPaymentTicket.getPayeeAcctName(),salaryPaymentTicket.getPayeeAcctNo()
            };
            paramsList.add(params);
        }
        super.getjdbcTemplate().batchUpdate(sql.toString(), new BatchPreparedStatementSetter() {
            public int getBatchSize() {
                return paramsList.size();
                //这个方法设定更新记录数，通常List里面存放的都是我们要更新的，所以返回list.size();
            }
            public void setValues(PreparedStatement ps, int i)throws SQLException {
                Object[] sme = paramsList.get(i);
                for(int a = 0; a < 29; a++){
                    ps.setObject(a+1,sme[a]);
                }

            }
        });
    }
    @Override
    public List<SalaryPaymentTicket> salaryPaymentBank(List<SalaryPaymentTicket> salaryPaymentBanks,String mofDivCode) {
        List<String> paramList= new ArrayList<>();
        for(SalaryPaymentTicket salaryPaymentBank:salaryPaymentBanks){
                paramList.add(salaryPaymentBank.getPayId());
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" select biz_key, create_time, update_time, agency_id, agency_name, pay_id, ")
                .append("pay_app_no, bgt_id, pay_app_amt, fund_type, payee_acct_name,payee_acct_no,")
                .append("payee_acct_bank_name, pay_type, pro_cat, gov_bgt_eco, dep_bgt_eco, pro, ")
                .append("use_name,is_deleted, mof_div_code, exp_func,business_id  ")
                .append(" from ")
                .append(TableName.ALARY_PAYMENT_REQUISITION_BACK)
                .append(" where is_deleted ='0' ");
        sql.append(" and ").append(inSql("pay_id", paramList));
        sql.append(" and ").append(" mof_div_code =? ");
        Object[] params= new Object[]{
                mofDivCode
        };
        //请求参数初始化
        return this.getJdbcTemplate().query(sql.toString(), new BeanPropertyRowMapper(SalaryPaymentTicket.class),params);

    }

}
